         
/**
 * 
 *
 * @File name:  TFixedAssetsDao.java 
 * @Create on:  2011-10-30 20:01:346
 * @Author   :  谭翔
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
package com.boho.estates.report.service;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.boho.estates.po.TFixedAssetsPO;

import cn.boho.framework.context.ActionContext;
import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.DAO;
import cn.boho.framework.po.DynaBeanMap;
import cn.boho.framework.po.CQExp;
import cn.boho.framework.po.Pager;
import cn.boho.framework.po.ComboPager;
import cn.boho.framework.service.FileServerService;
import cn.boho.framework.utils.DateUtils;
            
/**
 * 【资产基本信息:T_FIXED_ASSETS】的Dao操作类
 * 
 */
public class TFixedAssetsService {

	/**
	* 根据条件查询【资产基本信息:T_FIXED_ASSETS|】信息
	* @param con
	* @param fixedAssetsName 资产名称
	* @param fixedAssetsType 资产类型
	* @param fixedAssetsNature 资产性质
	* @param createDateBegin 创建日期
	* @param createDateEnd 创建日期
	* @param createBy 创建人
	* @param isValid 是否有效
	* @return
	* @throws Exception
	* @date2011-10-31
	* @author 谭翔
	* 
	*/
	public static String  exportEXCEL(ActionContext atx,Connection con,Pager pager,String fixedAssetsName,String fixedAssetsType,String fixedAssetsNature,String createDateBegin,String createDateEnd,String createBy,Integer isValid
			,String is_year,String is_mon,String dateStr,String dateFmt, String departIds)throws Exception{
		/*CQExp cqExp = CQExp.instance()
		.select("select BARCODE,FIXED_ASSETS_NAME,WITH_MANANGER,STORED_IN,STOCK_DATE,USE_YEAR,WARRANTY,SPECIFICATION,SUPPLIERS_NO,FACTORY_NO  "+
				
		" from  T_FIXED_ASSETS_DETAIL B left join T_FIXED_ASSETS A on A.FIXED_ASSETS_NO=B.FIXED_ASSETS_NO Where 1=1 ");
				
			if(createDateBegin != null){
				cqExp.str(" and B.CREATE_DATE >="+createDateBegin );
			}
			if(createDateEnd != null){
				cqExp.str(" and B.CREATE_DATE <="+createDateEnd );
			}
			
			*/
			//;
		String oper_date = " B.CREATE_DATE ";
		if(0==isValid){
			oper_date = " B.UPDATE_DATE ";
		}
		
		CQExp cqExp = CQExp.instance()
		//.select("select BARCODE,FIXED_ASSETS_NAME,WITH_MANANGER,STORED_IN,STOCK_DATE,USE_YEAR,WARRANTY,SPECIFICATION,SUPPLIERS_NO,FACTORY_NO  "+
				
				//" from  T_FIXED_ASSETS_DETAIL B  ")
				
				
				.select("select B.BARCODE,B.FIXED_ASSETS_NAME,C.NAME,B.STORED_IN,B.STOCK_DATE,B.USE_YEAR,B.WARRANTY,B.SPECIFICATION,D.SUPPLIERS_NAME,B.FACTORY_NO   from  T_FIXED_ASSETS_DETAIL B  "+ 

					"left join T_PERSONNAL C on B.WITH_MANANGER=C.PERSONNAL_NO "+
					"left join T_SUPPLIERS D on B.SUPPLIERS_NO=D.SUPPLIERS_NO ")
				
				
				.where();
			if(isValid != null){
				cqExp.str(" and B.IS_VALID ="+isValid );
			}
		
			if(("1").equals(is_year)){
				cqExp.str( " and date_format("+oper_date+",'%Y') = '"+dateStr+"'  and B.FIXED_ASSETS_TYPE ='1' ");
			}else if(("1").equals(is_mon)){
				cqExp.str( " and date_format("+oper_date+",'%Y-%m') = '"+dateStr+"' and B.FIXED_ASSETS_TYPE ='2' " );
			}else if(("1_2").equals(is_mon)){//本月规定资产增加或减少
				cqExp.str( " and date_format( "+oper_date+",'%Y-%m') = '"+dateStr+"' and B.FIXED_ASSETS_TYPE ='1' " );
			}
			else if(("all").equals(is_year)){//全部 新增或减少固定资产
				cqExp.str( "  and  B.FIXED_ASSETS_TYPE ='1' ");
			}
			else if(("y_2").equals(is_year)){//低值按年查询
				cqExp.str( " and date_format("+oper_date+",'%Y') = '"+dateStr+"'  and B.FIXED_ASSETS_TYPE ='2' ");
			}
			else if(("all_2").equals(is_year)){//全部 新增或减少低值
				cqExp.str( "  and  B.FIXED_ASSETS_TYPE ='2' ");
			}
			
			else {
				/*cqExp.filed(createDateBegin == null ? null:"B.CREATE_DATE",CQExp.GREATER_EQ,createDateBegin)
				.filed(createDateEnd == null ? null:"B.CREATE_DATE",CQExp.LESS_EQ,createDateEnd)
				.str(" and A.FIXED_ASSETS_TYPE ='1' ")
				;*/
				
				if(createDateBegin != null){
					cqExp.str(" and date_format("+oper_date+",'%Y-%m-%d') >='"+createDateBegin+"'" );
				}
				if(createDateEnd != null){
					cqExp.str(" and date_format("+oper_date+",'%Y-%m-%d') <='"+createDateEnd+"'" );
				}
				cqExp.str("  and B.FIXED_ASSETS_TYPE ='1'  " );
			}
			cqExp.str(" and GS_DEPARTMENT_NO in (" + departIds + ")");
			
		System.out.println(cqExp.getSql());
		
		Connection conn = atx.getConection();
		String[] title={"条形码","资产名称","使用管理人","存放地","购买日期","使用年限","保修期","规格","供应商","出厂编号"};
		String sql=cqExp.getSql();//"select * from T_FIXED_ASSETS";
		
		String filename="week_increased_report_"+System.currentTimeMillis()+".xls";
		//String filepath = "e:"+File.separator+filename;
		HttpServletRequest request = (HttpServletRequest)atx.getHttpRequest();
		String filepath = request.getRealPath("");
		//String path=FileServerService.sfinalfile;//取得当前目录的路径
		filepath = filepath.replace("\\", "/");
		filepath = filepath+"/temp";
		File f = new File(filepath);
		if (!f.exists()) {f.mkdirs();}
		filepath = filepath+File.separator+filename;
		createEXCEL(title,sql,filepath,conn);
		
		return filepath;
	}
	public static void createEXCEL(String[]title,String sql,String path,Connection conn) throws Exception{
		WritableWorkbook wwb = null;
		WritableSheet ws = null;	// 创建一个可写入的excel文件对象
		
		//Connection conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test;","sa","sa");
		Statement st=conn.createStatement();
		
		try {
			
			ResultSet rs=st.executeQuery(sql);
			 int i=0;
			    int j=0;
			     //构建Workbook对象, 只读Workbook对象
			     //：创建可写入的Excel工作薄
			     //WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(path));
			     wwb = Workbook.createWorkbook(new FileOutputStream(path));
			     ws = wwb.createSheet("sheet1", 0);

			  for( i=0;i<title.length;i++){
			       WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);
			       WritableCellFormat wcfF = new WritableCellFormat(wf);
			       Label labelCF = new Label(i, 0, title[i],wcfF);
			       ws.addCell(labelCF);
			      }
			      
			     i=1;
			     while (rs.next()) {
			      for(j=1;j<=title.length;j++)
			      {
			       Label labelC = new Label(j-1,i, rs.getString(j));
			       ws.addCell(labelC);
			   }
			      i++;
			    }
			    //写入Exel工作表
			    wwb.write();
			    
			    rs.close();
			    conn.commit();
			  }
			  catch (Exception e) {
				wwb.close();
			    System.out.println(e.toString());
			  }finally{
				//关闭Excel工作薄对象
				wwb.close();
				    
			  }
			
		} 
	

}
